# 在界面中输入批次号信息；
# 根据批次号在数据库中查询相应的表头信息；
# 根据批次号扫描服务器中的CA-13\CA-14文件夹，找到直角度测试数据的地址，并显示到界面,类似Select * from database where lotnumber="24032669" and processid="102"；
# 根据批次号扫描服务器中的DS-01\DS-02文件夹，找到尺寸测试数据的地址，并显示到界面；类似Select * from database where lotnumber="24032669" and processid="103"；
# 根据批次号扫描服务器中的MT-01\MT-02文件夹，找到频率及△F测试测试数据地址，并显示到界面；类似Select * from database where lotnumber="24032669" and processid="101"；
# 可以在频率数据地址中选择对应的地址（选择其中的4~6个）；
# 可以在△F数据地址中选择对应的地址（选择其中的两个）；
# 点击生成报告按钮，可以基于报告模板自动生成报告；
# 工程师对报告进行审核，如有异常，选择其他的文件，重新生成报告；
import os
import re
import openpyxl
import datetime
from datetime import datetime, timedelta
import pandas as pd
from django.conf import settings


# 系统时间关键参数提取和转换，格式为2024-4-19-14-9-13

def ProjectDataScanFromServer(LotNumber, FreqPath, AnglePath, DimensionPath):
    print(LotNumber, FreqPath, AnglePath, DimensionPath)
    for file in os.listdir(FreqPath):
        print(file)


def GetDataToReport():
    print("Start")
    # 以001项目24032669批次为例，其中有7个频率测试文件，1个直角度测试文件，一个尺寸测试文件
    # 当选中7个文件，点输出报告时，报错，
    # 当选中其中6个文件，点击输出报告时，正常输出；
    # 选中的频率文件
    # 选中的直角度文件
    # 选中的尺寸测试文件


def FreqDataGet():
    print("123")


def XinHaoQuery(LotNumber):
    # df.iloc方法，根据行、列的数字位置查询
    database = r"./database/dimension.xlsx"
    # 工序号	工序名称	设备名称
    df = pd.read_excel(database,
                       dtype={'批次号': str, '型号': str, '姓名': str, '工序号': str, '工序名称': str, '设备名称': str})
    # order_type=df.loc[dfxinhao, "型号"]
    order_type = df.loc[df['批次号'] == LotNumber, '型号'].values[0]
    name = df.loc[df['批次号'] == LotNumber, '姓名'].values[0]
    processid = df.loc[df['批次号'] == LotNumber, '工序号'].values[0]
    processname = df.loc[df['批次号'] == LotNumber, '工序名称'].values[0]
    devicename = df.loc[df['批次号'] == LotNumber, '设备名称'].values[0]

    # print(order_type)
    # print(df)
    # a=df.loc[0, '批次号']
    # print(a)
    # print(type(a))
    return order_type, name, processid, processname, devicename


def HeaderInformationQuery(h_lot_number, h_product_type, report_number, report_date,
                           customer_name, order_number, production_lot_number, specification_model,
                           lot_quantity, direction_angle, feeding_angle, frequency_range,
                           frequency_measurement_gap, customer_material_number, product_size,
                           appearance_sample_quantity, ma_accept, ma_reject, frequency_diff_limit,
                           frequency_diff_control, frequency_offset_degree):
    """
        h_lot_number,               # 批次号
        report_number,              # 报告编号
        report_date,                # 报告日期
        customer_name,              # 客户名称
        production_lot_number,      # 生产批号
        order_number,               # 订单号码
        specification_model,        # 规格型号
        lot_quantity,                 # 批量
        frequency_range,            # 频率范围
        direction_angle,            # 方向角度
        feeding_angle,              #投料角度
        frequency_measurement_gap,  # 频率测量间隙
        customer_material_number, # 出货产品料号
        product_size,               # 产品尺寸
        appearance_sample_quantity, #外观抽样数
        ma_accept                   #Ma-ACC
        ma_reject                   #Ma-REJ
        frequency_diff_limit        #△F规格值
        frequency_diff_control      #△F管控值
        size_data_address           # 尺寸数据地址
        angle_data_address          # 直角度数据地址
        frequency_data_address      # 频率数据地址
        df_data_address             # △F数据地址
        frequency_offset_degree     # 频率偏移度
        output_excel_path: 输出文件地址
    """
    report_number_date = ("报告编号：" + report_number +
                          "                                                                      日期："
                          + report_date)

    lot_quantity = lot_quantity + "PCS"

    pattern = re.compile(r"(\d\.\d{1,3})±(\d\.\d{1,3})\*(\d\.\d{1,3})±(\d\.\d{1,3})")
    new_product_size = re.search(pattern, product_size)
    product_width, product_length, product_width_limit, product_length_limit = 0, 0, "", ""
    if new_product_size:
        product_length = new_product_size.group(1)
        product_width = new_product_size.group(3)
        product_width_limit = str(round((float(new_product_size.group(3)) - 0.003), 3)) + "-" + str(
            round((float(new_product_size.group(3)) + 0.003), 3))
        product_length_limit = str(round((float(new_product_size.group(1)) - 0.003), 3)) + "-" + str(
            round((float(new_product_size.group(1)) + 0.003), 3))

    appearance_sample_quantity = "外观抽样数（" + str(appearance_sample_quantity) + "）PCS"

    return (
        report_number_date, customer_name, order_number, feeding_angle, production_lot_number,
        specification_model, lot_quantity, direction_angle, frequency_range,
        frequency_measurement_gap, customer_material_number, product_size,
        ma_accept, ma_reject,
        appearance_sample_quantity,
        frequency_diff_limit, frequency_diff_control, frequency_offset_degree, product_width, product_length,
        product_width_limit,
        product_length_limit
    )


def do_autogenerate_report(header, size_data_address,
                           angle_data_address, frequency_data_address, df_data_address, output_excel_path):
    # LotNumber = "24032669"
    save_file = output_excel_path
    template_file = settings.BASE_DIR / "datas/template/templateV1.0.xlsx"
    workbook = openpyxl.load_workbook(template_file)
    worksheet = workbook["OQC report"]
    # print(angle_data_address[0])
    df_angle = pd.read_csv(angle_data_address[0], encoding="shift-jis")
    df_dimmension_list = pd.read_excel(size_data_address[0], skiprows=0, nrows=10)
    # *************************************************************************************
    df_dff_head_0 = pd.read_excel(df_data_address[0], header=None, skiprows=0, nrows=1)
    df_dff_data_0 = pd.read_excel(df_data_address[0], header=None, skiprows=6, nrows=10)
    # print(df_dff_data)
    df_dff_0 = pd.concat([df_dff_head_0, df_dff_data_0], axis=0, ignore_index=True)
    df_dff_0.columns = df_dff_0.iloc[0]
    df_dff_0 = df_dff_0.iloc[1:]
    print(df_dff_0)
    print(type(df_dff_0))
    # print(df_dff_0.head())
    print(df_dff_0["Δf(KHz)"][1])
    # *************************************************************************************
    df_dff_head_1 = pd.read_excel(df_data_address[1], header=None, skiprows=0, nrows=1)
    df_dff_data_1 = pd.read_excel(df_data_address[1], header=None, skiprows=6, nrows=10)
    # print(df_dff_data)
    df_dff_1 = pd.concat([df_dff_head_1, df_dff_data_1], axis=0, ignore_index=True)
    df_dff_1.columns = df_dff_1.iloc[0]
    df_dff_1 = df_dff_1.iloc[1:]
    # print(df_dff_1)

    # print(df_dimmension_list)
    # print(df_angle)
    # *********表头数据解析与写入**************************
    HeadList = ["B3",
                # 报告编号、日期信息，格式为报告编号：QC2403243                                                                 日期：2024年03月20日"M5",
                "C4", "E4", "H4", "L4",  # 客户名称、订单号码、投料角度、Lot NO
                "C5", "F5", "H5", "M5",  # 规格型号、批量、方向角度、频率范围
                "C6", "G6", "K6",  # 频率测量间隙、出货产品料号、产品尺寸
                "M10", "M11",  # 允收拒收数量
                "B15",  # 外观抽样数量
                "C34", "D34",  # △F规格值, △F管控值
                "C38", "C40",  # 产品宽度，产品长度
                "D38", "D40"  # 产品宽度限值，产品长度限值
                ]
    for i in range(len(HeadList)):
        if i == 17:
            worksheet[HeadList[i]] = "W:" + str(header[i])
        elif i == 18:
            worksheet[HeadList[i]] = "L:" + str(header[i])
        else:
            # print(i)
            # print(HeadList[i])
            # print(a[i])
            # print(header[i])
            worksheet[HeadList[i]] = header[i]

        # ******直角度数据解析与写入******************
    AngleList = [
        "E42", "F42", "G42", "H42", "I42",
        "E43", "F43", "G43", "H43", "I43",
        "K42", "L42", "M42", "N42"
    ]
    Angle_max = df_angle['倒角度1:d'].max()
    Angle_min = df_angle['倒角度1:d'].min()
    Angle_delta = Angle_max - Angle_min
    if Angle_max <= 91 and Angle_min >= 89:
        Angle_result = "OK"
    else:
        Angle_result = "NG"
    for i in range(len(AngleList)):
        if i == 10:
            worksheet[AngleList[i]].value = Angle_max
        elif i == 11:
            worksheet[AngleList[i]].value = Angle_min
        elif i == 12:
            worksheet[AngleList[i]].value = Angle_delta
        elif i == 13:
            worksheet[AngleList[i]].value = Angle_result
        else:
            worksheet[AngleList[i]].value = df_angle['倒角度1:d'][i]

    # *********尺寸宽度数据解析与写入********************
    DimmensionListWidth = [
        "E38", "F38", "G38", "H38", "I38",
        "E39", "F39", "G39", "H39", "I39",
        # 2012/7/3更新
        "K38", "L38", "M38", "N38"
    ]
    dimmension_width_max_limit = float(header[17]) + 0.003
    dimmension_width_min_limit = float(header[17]) - 0.003
    dimmension_width_max = df_dimmension_list["短边测试数据"].max()
    dimmension_width_min = df_dimmension_list["短边测试数据"].min()
    dimmension_width_delta = dimmension_width_max - dimmension_width_min

    if dimmension_width_max <= dimmension_width_max_limit and dimmension_width_min > dimmension_width_min_limit:
        dimmension_width_result = "OK"
    else:
        dimmension_width_result = "NG"
    for i in range(len(DimmensionListWidth)):
        if i == 10:
            worksheet[DimmensionListWidth[i]].value = dimmension_width_max
        elif i == 11:
            worksheet[DimmensionListWidth[i]].value = dimmension_width_min
        elif i == 12:
            worksheet[DimmensionListWidth[i]].value = dimmension_width_delta
        elif i == 13:
            worksheet[DimmensionListWidth[i]].value = dimmension_width_result
        else:
            # print(i)
            # print(df_dimmension_list["短边测试数据"][i])
            worksheet[DimmensionListWidth[i]].value = df_dimmension_list["短边测试数据"][i]
    # **************尺寸长度数据解析与写入********************
    DimmensionListLength = [
        "E40", "F40", "G40", "H40", "I40",
        "E41", "F41", "G41", "H41", "I41",
        # 2012/7/3更新
        "K40", "L40", "M40", "N40"
    ]
    dimmension_length_max_limit = float(header[18]) + 0.003
    dimmension_length_min_limit = float(header[18]) - 0.003
    dimmension_length_max = df_dimmension_list["长边测试数据"].max()
    dimmension_length_min = df_dimmension_list["长边测试数据"].min()
    dimmension_length_delta = dimmension_length_max - dimmension_length_min
    if dimmension_length_max <= dimmension_length_max_limit and dimmension_length_min > dimmension_length_min_limit:
        dimmension_length_result = "OK"
    else:
        dimmension_length_result = "NG"

    for i in range(len(DimmensionListLength)):
        if i == 10:
            worksheet[DimmensionListLength[i]].value = dimmension_length_max
        elif i == 11:
            worksheet[DimmensionListLength[i]].value = dimmension_length_min
        elif i == 12:
            worksheet[DimmensionListLength[i]].value = dimmension_length_delta
        elif i == 13:
            worksheet[DimmensionListLength[i]].value = dimmension_length_result
        else:
            # print(i)
            # print(df_dimmension_list["长边测试数据"][i])
            worksheet[DimmensionListLength[i]].value = df_dimmension_list["长边测试数据"][i]
        # *********△F数据解析与写入********************
        FdiffList = [
            [
                "E34", "F34", "G34", "H34", "I34",
                "E35", "F35", "G35", "H35", "I35",
                # 2024/7/3新增
                "K34", "L34", "M34", "N34"

            ],
            [
                "E36", "F36", "G36", "H36", "I36",
                "E37", "F37", "G37", "H37", "I37",
                # 2024/7/3新增
                "K36", "L36", "M36", "N36"
            ]

        ]

        print(len(header))
        print(header[16])
        df_limits = header[16]
        pattern = re.compile(r"(-\d*)～(-\d*)")
        new_df_limits = re.search(pattern, df_limits)

        if new_df_limits:
            df_limits_max = float(new_df_limits.group(1))
            df_limits_min = float(new_df_limits.group(2))

            print(df_limits_max, df_limits_min)
        for i in range(len(df_data_address)):
            print(df_data_address[i])
            df_data_add = df_data_address[i]
            df_df_data = pd.concat(
                [pd.read_excel(df_data_add, header=None, skiprows=0, nrows=1),
                 pd.read_excel(df_data_add, header=None, skiprows=6, nrows=10)],
                axis=0, ignore_index=True
            )
            df_df_data.columns = df_df_data.iloc[0]
            df_df_data = df_df_data.iloc[1:]
            # print(df_freq_data)
            print(df_df_data["Δf(KHz)"])
            df_df_data_max = -df_df_data["Δf(KHz)"].min()
            df_df_data_min = -df_df_data["Δf(KHz)"].max()
            df_df_data_delta = df_df_data_max - df_df_data_min
            if df_df_data_max <= df_limits_max and df_df_data_min >= df_limits_min:
                df_df_result = "OK"
            else:
                df_df_result = "NG"
            for j in range(len(FdiffList[i])):
                if j == 10:
                    worksheet[FdiffList[i][j]].value = df_df_data_max
                elif j == 11:
                    worksheet[FdiffList[i][j]].value = df_df_data_min
                elif j == 12:
                    worksheet[FdiffList[i][j]].value = df_df_data_delta
                elif j == 13:
                    worksheet[FdiffList[i][j]].value = df_df_result
                else:
                    worksheet[FdiffList[i][j]].value = -df_df_data["Δf(KHz)"][j + 1]

        # *********************频率数据解析与写入*************************************
        # 对地址长度进行判断，如果长度大于6，报错；
        # 如果地址长度小于6，开始进行解析
        # 提取文件名中的频段信息，最小值减去10得到下限值，最大值增加10得到上限值；
        # 频段信息和上下限信息写入；
        # 频率值信息写入；
    FreqList = [
        [
            "E22", "F22", "G22", "H22", "I22",
            "E23", "F23", "G23", "H23", "I23",
            "C22", "D22", "N22",
            # 2024/7/3新增
            "K22", "L22", "M22"
        ],
        [
            "E24", "F24", "G24", "H24", "I24",
            "E25", "F25", "G25", "H25", "I25",
            "C24", "D24", "N24",
            # 2024/7/3新增
            "K24", "L24", "M24"

        ],
        [
            "E26", "F26", "G26", "H26", "I26",
            "E27", "F27", "G27", "H27", "I27",
            "C26", "D26", "N26",
            # 2024/7/3新增
            "K26", "L26", "M26"

        ],
        [
            "E28", "F28", "G28", "H28", "I28",
            "E29", "F29", "G29", "H29", "I29",
            "C28", "D28", "N28",
            # 2024/7/3新增
            "K28", "L28", "M28"
        ],
        [
            "E30", "F30", "G30", "H30", "I30",
            "E31", "F31", "G31", "H31", "I31",
            "C30", "D30", "N30",
            # 2024/7/3新增
            "K30", "L30", "M30"
        ],
        [
            "E32", "F32", "G32", "H32", "I32",
            "E33", "F33", "G33", "H33", "I33",
            "C32", "D32", "N32",
            # 2024/7/3新增
            "K32", "L32", "M32"
        ]
    ]
    print(frequency_data_address)
    print(len(frequency_data_address))
    worksheet["B22"].value = "频率抽检数量(" + str(len(frequency_data_address) * 10) + ")pcs"
    for i in range(len(frequency_data_address)):
        print(frequency_data_address[i])
        freq_data_add = frequency_data_address[i]
        pattern = re.compile(r".*101_频率及△F测试_MT-0\d_(\d*)-(\d*).*")
        new_freq_name = re.search(pattern, freq_data_add)
        df_freq_data = pd.concat(
            [pd.read_excel(freq_data_add, header=None, skiprows=0, nrows=1),
             pd.read_excel(freq_data_add, header=None, skiprows=6, nrows=10)],
            axis=0, ignore_index=True
        )
        df_freq_data.columns = df_freq_data.iloc[0]
        df_freq_data = df_freq_data.iloc[1:]
        # print(df_freq_data)
        print(df_freq_data["基频频率(KHz)"])
        df_freq_data_max = df_freq_data["基频频率(KHz)"].max()
        df_freq_data_min = df_freq_data["基频频率(KHz)"].min()
        # ****************2024/7/3新增
        df_freq_data_delta = df_freq_data_max - df_freq_data_min

        if new_freq_name:
            freq_spec = new_freq_name.group(1) + "-" + new_freq_name.group(2)
            freq_limits = str(int(new_freq_name.group(1)) - 10) + "-" + str(int(new_freq_name.group(2)) + 10)
            print(freq_spec)
            print(freq_limits)
            freq_limits_low = int(new_freq_name.group(1)) - 10
            freq_limits_high = int(new_freq_name.group(2)) + 10
            print(freq_limits_high, freq_limits_low)
        if df_freq_data_max <= freq_limits_high and df_freq_data_min >= freq_limits_low:
            print(df_freq_data_max, freq_limits_high)
            print(df_freq_data_min, freq_limits_low)
            df_freq_data_result = "OK"
        else:
            print(df_freq_data_max, freq_limits_high)
            print(df_freq_data_min, freq_limits_low)
            df_freq_data_result = "NG"
        print(FreqList[i])
        for j in range(len(FreqList[i])):
            print(FreqList[i][j])
            if j == 10:
                worksheet[FreqList[i][j]].value = freq_spec
            elif j == 11:
                worksheet[FreqList[i][j]].value = freq_limits
            elif j == 12:
                worksheet[FreqList[i][j]].value = df_freq_data_result
            elif j == 13:
                worksheet[FreqList[i][j]].value = df_freq_data_max
            elif j == 14:
                worksheet[FreqList[i][j]].value = df_freq_data_min
            elif j == 15:
                worksheet[FreqList[i][j]].value = df_freq_data_delta

            else:
                worksheet[FreqList[i][j]].value = df_freq_data["基频频率(KHz)"][j + 1]
    workbook.save(save_file)


if __name__ == '__main__':
    in_dir = r"D:\workbench\ant-pss-private-projects\P20240625出货报告自动生成系统\oqc_report_autogenerate\datas\in"
    server_dir = r"D:\workbench\ant-pss-private-projects\P20240625出货报告自动生成系统\oqc_report_autogenerate\datas\server"
    out_dir = r"D:\workbench\ant-pss-private-projects\P20240625出货报告自动生成系统\oqc_report_autogenerate\datas\output"
    Fpath_MT01 = rf"{server_dir}\MT-01/"
    Apath_CA13 = rf"{server_dir}\CA-13"
    Dpath_DS01 = rf"{server_dir}\DS-01"
    LotNumber = "24032669"
    size_data_add = [
        f'{in_dir}\project\\001\\24032669_38403013_103_尺寸测试_DS-01_ethan_2024-05-24-17-51-05.xlsx']
    angle_data_add = [
        f'{in_dir}\project\\001\\24032669_38403013_102_直角度测试_CA-13_ethan_2024-04-05-19-41-00.csv']
    frequency_data_add = [
        f'{in_dir}\project\\001\\24032669_38403013_101_频率及△F测试_MT-01_39890-39900_ethan_2024-04-21-11-04-29.xls',
        f'{in_dir}\project\\001\\24032669_38403013_101_频率及△F测试_MT-01_39900-39910_ethan_2024-04-23-14-29-52.xls',
        f'{in_dir}\project\\001\\24032669_38403013_101_频率及△F测试_MT-01_39910-39920_ethan_2024-04-21-11-08-50.xls',
        f'{in_dir}\project\\001\\24032669_38403013_101_频率及△F测试_MT-01_39920-39930_ethan_2024-04-21-11-11-47.xls'
    ]
    df_data_add = [
        f'{in_dir}\project\\001\\24032669_38403013_101_频率及△F测试_MT-01_39890-39900_ethan_2024-04-21-11-04-29.xls',
        f'{in_dir}\project\\001\\24032669_38403013_101_频率及△F测试_MT-01_39900-39910_ethan_2024-04-23-14-29-52.xls'
    ]
    now = datetime.now()  # 系统时间提取，格式为2024-04-19 14:09:13.723344
    nowtime = str(now.year) + "-" + str(now.month) + "-" + str(now.day) + "-" \
              + str(now.hour) + "-" + str(now.minute) + "-" + str(now.second)

    output_excel_path = rf"{out_dir}/" + LotNumber + "_晶片OQC出货报告_" + nowtime + ".xlsx"

    # ProjectDataScanFromServer(LotNumber,Fpath_MT01,Apath_CA13,Dpath_DS01)
    header_information = HeaderInformationQuery(LotNumber)
    do_autogenerate_report(header_information, size_data_add, angle_data_add, frequency_data_add, df_data_add,
                           output_excel_path)
